const connection = require("../app/database");

class userService {
  async create(userInfo) {
    // 1.获取用户
    const { account, password } = userInfo;
    // 2.拼接statement
    // 拼接昵称
    const name = account + "-";
    // 插入用户名和密码
    const statement =
      "INSERT INTO `user` (account,password,name) VALUES (?,?,?);";
    // 3.执行SQL语句
    //   这个执行语句是异步操作，要等待异步执行完的结果再进行下一步的操作
    //   解构出这个数组类型中的result就行
    const [result] = await connection.execute(statement, [
      account,
      password,
      name,
    ]);

    // console.log(result.insertId);
    // 4.添加权限
    const result1 = await this.addAuthority(result.insertId, "1");
    // console.log(result1);
    return result1;
  }

  // 查询是否有该账号的用户
  async findUserByACCOUNT(account) {
    // AND is_delete = 0
    const statement = "SELECT * FROM `user` WHERE account = ?";
    // 3.执行SQL语句
    //   这个执行语句是异步操作，要等待异步执行完的结果再进行下一步的操作
    //   解构出这个数组类型中的result就行
    const [result] = await connection.execute(statement, [account]);

    return result;
  }

  // 查询该账户用户是否注销
  async findUserByACCOUNT(account) {
    const statement = "SELECT * FROM `user` WHERE account = ?";
    // 3.执行SQL语句
    //   这个执行语句是异步操作，要等待异步执行完的结果再进行下一步的操作
    //   解构出这个数组类型中的result就行
    const [result] = await connection.execute(statement, [account]);

    return result;
  }

  /**
   * 添加用户的权限
   * @param {用户id} userId
   * @param {权限} root
   * 1:Common 普通
   * 2:Member 会员
   * 3:Admin 管理
   */
  async addAuthority(userId, root) {
    const statement =
      "INSERT INTO `user_authority` (user_id,authority_id) VALUES (?,?);";
    const [result] = await connection.execute(statement, [userId, root]);

    return result;
  }

  /**
   * 分页获取用户
   * @param {每次请求的数据} limit
   * @param {数据的偏移量} offset
   * @returns
   */
  async getUsers(limit, offset) {
    const statement = `
      SELECT 
      u.id id, u.account account, u.avatar avatar,u.createAt createTime,u.user_address address,
			JSON_ARRAYAGG(JSON_OBJECT(
				'id',a.id, 'name',a.name
			)) authority
      FROM user u
      LEFT JOIN user_authority ua ON u.id = ua.user_id
			LEFT JOIN authority a ON ua.authority_id = a.id
			GROUP BY u.id
      LIMIT ? OFFSET ?
      `;
    const [result] = await connection.execute(statement, [limit, offset]);

    return result;
  }

  // 查询用户
  async inquireUser(id) {
    const statement = `
    SELECT 
    u.id id, u.account account,u.name name, u.avatar avatar,u.createAt createTime,u.user_address address,
    JSON_ARRAYAGG(JSON_OBJECT(
      'id',a.id, 'name',a.name
    )) authority
    FROM user u
    LEFT JOIN user_authority ua ON u.id = ua.user_id
    LEFT JOIN authority a ON ua.authority_id = a.id
    where u.id = ?
    GROUP BY u.id
    `;
    const [result] = await connection.execute(statement, [id]);

    return result;
  }

  // 更新用户昵称
  async updateNameService(id, newName) {
    const statement = `
    UPDATE user SET name = ? WHERE id = ?
    `;
    const [result] = await connection.execute(statement, [newName, id]);

    return result;
  }

  // 更新用户头像
  async updateAvatarService(id, newAvatar) {
    const statement = `
    UPDATE user SET avatar = ? WHERE id = ?
    `;
    const [result] = await connection.execute(statement, [newAvatar, id]);

    return result;
  }

  // 获取用户关注
  async getUserAttentionService(id, limit, offset) {
    const attentions = {
      attentionLists: [],
      attentionTotal: 0,
    };

    const statement1 =
      "SELECT COUNT(*) as total FROM attention WHERE attention.uid = ?";
    const [attentionTotalResult] = await connection.execute(statement1, [id]);
    attentions.attentionTotal = attentionTotalResult[0].total;

    const statement2 = `
      SELECT 
      u.id id, u.account account, u.name name,u.avatar avatar,u.createAt createTime,u.user_address address,
			JSON_ARRAYAGG(JSON_OBJECT(
				'id',a.id, 'name',a.name
			)) authority
      FROM user u
			LEFT JOIN attention att ON u.id = att.attention_uid
      LEFT JOIN user_authority ua ON u.id = ua.user_id
			LEFT JOIN authority a ON ua.authority_id = a.id
			where att.uid = ?
			GROUP BY u.id
      LIMIT ? OFFSET ?
      `;
    const [attentionListsResult] = await connection.execute(statement2, [
      id,
      limit,
      offset,
    ]);
    attentions.attentionLists = attentionListsResult;

    return attentions;
  }

  // 获取用户收藏
  async getUserSaveService(id, limit, offset) {
    const data = {
      lists: [],
      total: 0,
    };

    try {
      const statement1 =
        "SELECT COUNT(*) as total FROM article_save s WHERE s.uid = ?";
      const [result1] = await connection.execute(statement1, [id]);
      data.total = result1[0].total;

      if (!result1[0].total) {
        return data;
      }

      const statement2 = `
        SELECT 
        a.id id, a.title title,a.summary summary,a.body body, 
        a.type type,a.weight weight,a.view_counts view_counts,
        CASE
          WHEN a.cover3 IS NOT NULL AND a.cover3 != '' THEN JSON_ARRAY(a.cover1, a.cover2, a.cover3)
          WHEN a.cover2 IS NOT NULL AND a.cover2 != '' THEN JSON_ARRAY(a.cover1, a.cover2)
          WHEN a.cover1 IS NOT NULL AND a.cover1 != '' THEN JSON_ARRAY(a.cover1)
          ELSE JSON_ARRAY()
        END AS covers,
        a.createAt createTime, a.updateAt updateTime,
        a.audit_reason audit_reason,a.audit_time audit_time,
        JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account,
        'fans_counts', (SELECT COUNT(*) FROM attention WHERE attention_uid = u.id),
        'attention_counts',(SELECT COUNT(*) FROM attention WHERE uid = u.id),
        'support_counts',(SELECT COUNT(*) FROM article_support WHERE article_uid = u.id)) AS user,
        JSON_OBJECT('id',c.id,'label',c.category_label,'name',c.category_name,'avatar',c.category_avatar,'account',c.article_count) AS category,
        (SELECT COUNT(*) FROM article_save WHERE article_id = a.id) AS save_counts,
        (SELECT COUNT(*) FROM article_support WHERE article_id = a.id) AS support_counts,
        (SELECT 
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    'id', ac.id,
                    'content', ac.content,'commentId',ac.comment_id,
                    'user', JSON_OBJECT('id', u.id, 'name', u.name,'avatar',u.avatar,'account',u.account),
                    'createAt', ac.createAt,
                    'updateAt', ac.updateAt
                )
            )
          FROM article_comment ac
          LEFT JOIN user u ON u.id = ac.user_id
          WHERE ac.article_id = a.id) AS comments
        FROM article a
        LEFT JOIN user u ON u.id = a.uid
        LEFT JOIN category c ON c.id = a.category_id
        WHERE a.id IN (
          SELECT article_id
          FROM article_save
          WHERE uid = ?
        )			
        AND a.is_delete = 0 
        AND a.audit_state = 2
        ORDER BY weight DESC
        LIMIT ? OFFSET ?
      `;
      const [result2] = await connection.execute(statement2, [
        id,
        limit,
        offset,
      ]);
      data.lists = result2;

      return data;
    } catch (error) {
      console.log(error);
    }
  }

  // 获取用户点赞
  async getUserSupportService(id, limit, offset) {
    const data = {
      lists: [],
      total: 0,
    };

    try {
      const statement1 =
        "SELECT COUNT(*) as total FROM article_support s WHERE s.uid = ?";
      const [result1] = await connection.execute(statement1, [id]);
      data.total = result1[0].total;

      if (!result1[0].total) {
        return data;
      }

      const statement2 = `
        SELECT 
        a.id id, a.title title,a.summary summary,a.body body, 
        a.type type,a.weight weight,a.view_counts view_counts,
        CASE
          WHEN a.cover3 IS NOT NULL AND a.cover3 != '' THEN JSON_ARRAY(a.cover1, a.cover2, a.cover3)
          WHEN a.cover2 IS NOT NULL AND a.cover2 != '' THEN JSON_ARRAY(a.cover1, a.cover2)
          WHEN a.cover1 IS NOT NULL AND a.cover1 != '' THEN JSON_ARRAY(a.cover1)
          ELSE JSON_ARRAY()
        END AS covers,
        a.createAt createTime, a.updateAt updateTime,
        a.audit_reason audit_reason,a.audit_time audit_time,
        JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account,
        'fans_counts', (SELECT COUNT(*) FROM attention WHERE attention_uid = u.id),
        'attention_counts',(SELECT COUNT(*) FROM attention WHERE uid = u.id),
        'support_counts',(SELECT COUNT(*) FROM article_support WHERE article_uid = u.id)) AS user,
        JSON_OBJECT('id',c.id,'label',c.category_label,'name',c.category_name,'avatar',c.category_avatar,'account',c.article_count) AS category,
        (SELECT COUNT(*) FROM article_save WHERE article_id = a.id) AS save_counts,
        (SELECT COUNT(*) FROM article_support WHERE article_id = a.id) AS support_counts,
        (SELECT 
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    'id', ac.id,
                    'content', ac.content,'commentId',ac.comment_id,
                    'user', JSON_OBJECT('id', u.id, 'name', u.name,'avatar',u.avatar,'account',u.account),
                    'createAt', ac.createAt,
                    'updateAt', ac.updateAt
                )
            )
          FROM article_comment ac
          LEFT JOIN user u ON u.id = ac.user_id
          WHERE ac.article_id = a.id) AS comments
        FROM article a
        LEFT JOIN user u ON u.id = a.uid
        LEFT JOIN category c ON c.id = a.category_id
        WHERE a.id IN (
          SELECT article_id
          FROM article_support
          WHERE uid = ?
        )			
        AND a.is_delete = 0 
        AND a.audit_state = 2
        ORDER BY weight DESC
        LIMIT ? OFFSET ?
      `;
      const [result2] = await connection.execute(statement2, [
        id,
        limit,
        offset,
      ]);
      data.lists = result2;

      return data;
    } catch (error) {
      console.log(error);
    }
  }

  // 获取用户发布
  async getUserPublishService(id, limit, offset) {
    const data = {
      lists: [],
      total: 0,
    };

    try {
      const statement1 =
        "SELECT COUNT(*) as total FROM article a WHERE a.uid = ?";
      const [result1] = await connection.execute(statement1, [id]);
      data.total = result1[0].total;

      if (!result1[0].total) {
        return data;
      }

      const statement2 = `
      SELECT 
      a.id id, a.title title,a.summary summary,a.body body, 
			a.type type,a.weight weight,a.view_counts view_counts,
			CASE
				WHEN a.cover3 IS NOT NULL AND a.cover3 != '' THEN JSON_ARRAY(a.cover1, a.cover2, a.cover3)
				WHEN a.cover2 IS NOT NULL AND a.cover2 != '' THEN JSON_ARRAY(a.cover1, a.cover2)
				WHEN a.cover1 IS NOT NULL AND a.cover1 != '' THEN JSON_ARRAY(a.cover1)
				ELSE JSON_ARRAY()
			END AS covers,
			a.createAt createTime, a.updateAt updateTime,
			a.audit_reason audit_reason,a.audit_time audit_time,
			JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account,
			'fans_counts', (SELECT COUNT(*) FROM attention WHERE attention_uid = u.id),
			'attention_counts',(SELECT COUNT(*) FROM attention WHERE uid = u.id),
			'support_counts',(SELECT COUNT(*) FROM article_support WHERE article_uid = u.id)) AS user,
			JSON_OBJECT('id',c.id,'label',c.category_label,'name',c.category_name,'avatar',c.category_avatar,'account',c.article_count) AS category,
			JSON_OBJECT('id',audit.audit_id,'state',audit.audit_state) AS audit,
			(SELECT COUNT(*) FROM article_save WHERE article_id = a.id) AS save_counts,
			(SELECT COUNT(*) FROM article_support WHERE article_id = a.id) AS support_counts,
			(SELECT 
					JSON_ARRAYAGG(
							JSON_OBJECT(
									'id', ac.id,
									'content', ac.content,'commentId',ac.comment_id,
									'user', JSON_OBJECT('id', u.id, 'name', u.name,'avatar',u.avatar,'account',u.account),
									'createAt', ac.createAt,
									'updateAt', ac.updateAt
							)
					)
				FROM article_comment ac
				LEFT JOIN user u ON u.id = ac.user_id
				WHERE ac.article_id = a.id) AS comments
      FROM article a
      LEFT JOIN user u ON u.id = a.uid
			LEFT JOIN category c ON c.id = a.category_id
			LEFT JOIN audit ON audit.audit_id = a.audit_state
			WHERE u.id = ?
			AND a.is_delete = 0 
			ORDER BY weight DESC
			LIMIT ? OFFSET ?
      `;
      const [result2] = await connection.execute(statement2, [
        id,
        limit,
        offset,
      ]);
      data.lists = result2;

      return data;
    } catch (error) {
      console.log(error);
    }
  }

  // 关注用户
  async attentionUserService(uid, attention_uid) {
    const statement = `
    INSERT INTO attention (uid,attention_uid) VALUES (?,?);
    `;
    const [result] = await connection.execute(statement, [uid, attention_uid]);

    return result;
  }

  // 取消关注用户
  async cancelAttentionUserService(uid, attention_uid) {
    const statement = `
    DELETE FROM attention WHERE uid = ? AND attention_uid = ?;
    `;
    const [result] = await connection.execute(statement, [uid, attention_uid]);

    return result;
  }

  // 获取是否关注用户
  async isAttentionUserService(uid, attention_uid) {
    const statement = `
    SELECT COUNT(*) AS isAttention FROM attention WHERE uid = ? AND attention_uid = ?
    `;
    const [result] = await connection.execute(statement, [uid, attention_uid]);

    return result[0];
  }

  // 获取是否收藏文章
  async isSaveArticleService(uid, article_id) {
    const statement = `
    SELECT COUNT(*) AS isSave FROM article_save WHERE article_id = ? AND uid = ?
    `;
    const [result] = await connection.execute(statement, [article_id, uid]);

    return result[0];
  }

  // 获取是否点赞文章
  async isSupportArticleService(uid, article_id) {
    const statement = `
    SELECT COUNT(*) AS isSupport FROM article_support WHERE article_id = ? AND uid = ?
    `;
    const [result] = await connection.execute(statement, [article_id, uid]);

    return result[0];
  }

  // 推荐用户
  async recommendUsersService(uid, limit) {
    if (!uid) {
      try {
        const statement1 = `
      SELECT 
      u.id id, u.account account,u.name name, u.avatar avatar,u.createAt createTime,u.user_address address,
			 (SELECT COUNT(*) FROM attention WHERE attention_uid = u.id) AS fans_counts,
			JSON_ARRAYAGG(JSON_OBJECT(
				'id',a.id, 'name',a.name
			)) authority
      FROM user u
      LEFT JOIN user_authority ua ON u.id = ua.user_id
			LEFT JOIN authority a ON ua.authority_id = a.id
			GROUP BY u.id
			ORDER BY RAND() 
      LIMIT ?
    `;
        const [result] = await connection.execute(statement1, [limit]);

        return result;
      } catch (error) {
        console.log(error);
      }
    } else {
      try {
        const statement2 = `
        SELECT 
      u.id id, u.account account,u.name name, u.avatar avatar,u.createAt createTime,u.user_address address,
			 (SELECT COUNT(*) FROM attention WHERE attention_uid = u.id) AS fans_counts,
			JSON_ARRAYAGG(JSON_OBJECT(
				'id',a.id, 'name',a.name
			)) authority,
			EXISTS(SELECT 1 FROM attention WHERE attention_uid = u.id AND uid = ?) AS is_following
      FROM user u
      LEFT JOIN user_authority ua ON u.id = ua.user_id
			LEFT JOIN authority a ON ua.authority_id = a.id
			GROUP BY u.id
			ORDER BY RAND() 
      LIMIT ? 
      `;
        const [result] = await connection.execute(statement2, [uid, limit]);

        return result;
      } catch (error) {
        console.log(error);
      }
    }
  }

  // 获取用户所在地区救助热线
  async getUserRescueHotlineService(province) {
    try {
      const statement1 = `
      SELECT * FROM line where region = ?
    `;
      const [result] = await connection.execute(statement1, [province]);

      return result;
    } catch (error) {
      console.log(error);
    }
  }
}

module.exports = new userService();
